﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;

namespace T01WPF
{
    /// <summary>
    /// User的数据访问类：增、删、改、查等操作
    /// </summary>
    class UserDao
    {
        //MYSQL的数据连接串描述
        private static string connStr 
            = "server=localhost;user=root;database=test";


        //private static string connStr = "server=localhost;user=root;database=test";ysql

        /// <summary>
        /// 登陆检查
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>0：无效 1：登陆成功</returns>
        public static int LoginCheck(string username,string password)
        {
            //默认为无效的用户名和密码
            int number = 0;
            //1.构建MySQl连接对象
            MySqlConnection conn = new MySqlConnection(connStr);
            
            try
            {
                //2.打开数据库连接
            conn.Open();
                //比较用户名与密码的SQL语句
                string sql = "SELECT COUNT(id) FROM `tb_users` WHERE `username`='" + username + "`AND` password' ='" + password + "'";
                Debug.WriteLine("SQL语句：" + sql);
                //3.创建SQL命令
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //4.执行SQL命令
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    number = Convert.ToInt32(result);
                }


            }
            catch (Exception ex) {
                Debug.WriteLine("数据库访问异常...");
                Debug.WriteLine("=====================");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("=====================");
           
            }
            finally {
                if (conn != null)
                    conn.Close();
            }
            
            return number;
            }

        /// <summary>
        /// 获取所有用户列表
        /// </summary>
        /// <returns></returns>
        public static List<User>LoadData()
        {
            //构建用户集合
            List<User> userList = new List<User>();

            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                conn.Open();
                string sql = "SELECT * FROM tb_users";
                MySqlCommand cmd = new MySqlCommand(sql, conn); ;
                //3.获取数据访问的游标（当前记录）
                MySqlDataReader rdr = cmd.ExecuteReader();
                //4.依次访问数据记录并加入数据集合
                while (rdr.Read())
                {
                    User user = new User();
                    user.ID = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.IsAdmin = Convert.ToInt16(rdr["is_adnmin"]) == 1 ? true : false;
                    userList.Add(user);
                }
                //5.关闭游标
                rdr.Close();
            }
            catch (Exception ex)
            {

                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }

            //throw new NotImplementedException();
            return userList;
        }

        /// <summary>
        /// 按条件获取用户列表
        /// </summary>
        /// <param name="condition">条件参数值</param>
        /// <returns>符合条件的用户集合</returns>
        public static List<User> LoadData(string condition) {
            //构建用户集合
            List<User> userList = new List<User>();
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM tb_users WHERE username LIKE @username OR nickname LIKE";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数(占位符)
                cmd.Parameters.AddWithValue("@username", "%" + condition + "%");
                cmd.Parameters.AddWithValue("@nickname", "%" + condition + "%");
               
                //3.获取数据访问的游标（当前记录）
                MySqlDataReader rdr = cmd.ExecuteReader();
                //4.依次访问数据记录并加入数据集合
                while (rdr.Read())
                {
                    User user = new User();
                    user.ID = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.IsAdmin = Convert.ToInt16(rdr["is_adnmin"]) == 1 ? true : false;
                    userList.Add(user);//加到用户列表
                }
                //5.关闭游标
                rdr.Close();
            }
            catch (Exception ex)
            {

                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }

            //throw new NotImplementedException();
            return userList;
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="user">用户数据实体</param>
        /// <returns>MySQL自增ID</returns>
        public static long Insert(User user) {
            long insertID = 0;

            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                //2.打开数据库连接
                conn.Open();
                //比较用户名与密码的SQL语句
                string sql = "INSERT INTO  tb_users" + "(username, password, nickname, is_admin) " + "VALUES" + "(@username,@passwoed,@nickname,@is_admin) ";
                //Debug.WriteLine("SQL语句：" + sql);
                //3.创建SQL命令
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                //参数绑定
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);

                //4.执行SQL命令
                object result = cmd.ExecuteNonQuery();
                insertID = cmd.LastInsertedId;
            }catch (Exception ex)
            {
                Debug.WriteLine("数据插入异常" + ex.ToString());
            }
            finally {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return insertID;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">用户数据实体</param>
        public static void Update(User user)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                //2.打开数据库连接
                conn.Open();
                //比较用户名与密码的SQL语句
                string sql = "UPDATE tb_users SET " + "username=@username, "
                                                    + "password=@password, "
                                                    + "nickname=@nickname, "
                                                    + "is_admin=@is_admin "
                                                    + "WHERE id=@id";
                //Debug.WriteLine("SQL语句：" + sql);
                //3.创建SQL命令
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                //参数绑定
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);

                //4.执行SQL命令
                object result = cmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据更新异常" + ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public static void Delect(long id) {
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                string sql = "DELECT FROM tb_users WHERE id=@id";
                MySqlCommand cmd = new MySqlCommand(sql,conn);
                cmd.Parameters.AddWithValue("@id",id);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据删除错误" + ex.ToString());
            }
            finally {
                if (conn != null)
                    conn.Close();
            }
        }
    }
    }
    

